<?php
namespace Admin\Controller;
use Think\Controller;
use Admin\Controller\ChargeController;

/**
 * 公租房业务
 * Class GzfController
 * @package Admin\Controller
 */
class GzfController extends AdminController {

    //控制器名称
    public static $name = "公租房";

    //公租房业务申请状态
    public static $status = array(
        'submit'    =>  "待资格审查",
        'accept'    =>  "待联合初审",
        'pass1'     =>  "待房产局复审",
        'pass2'     =>  "待房产局审批",
        'pass3'     =>  "待资格公示",
        'pass4'     =>  "待摇号分房",
        'done'      =>  "申请成功",
        'refuse'    =>  "退出保障",
        'failed'    =>  "申请失败",
        'all'       =>  "申请汇总"
    );

    //对不同的状态，设置不同的formview模板
    public static $tmpl = array(
        'all'       =>  array("listview"=>"index", "formview"=>"detail_all"),
        'submit'    =>  array("listview"=>"index", "formview"=>"detail"),
        'accept'    =>  array("listview"=>"index", "formview"=>"detail_lhcs"),
        'pass1'     =>  array("listview"=>"index", "formview"=>"detail_fcjcs"),
        'pass2'     =>  array("listview"=>"index", "formview"=>"detail_fcjfs"),
        'pass3'     =>  array("listview"=>"index", "formview"=>"detail_zggs"),
        'pass4'     =>  array("listview"=>"index", "formview"=>"detail_done"),
        'done'      =>  array("listview"=>"index", "formview"=>"detail_done"),
        'refuse'    =>  array("listview"=>"index", "formview"=>"detail"),
        'failed'    =>  array("listview"=>"index", "formview"=>"detail"),
    );

    /**
     * 业务办理 -> 公租房业务 -> 公租房申请 -> listview
     */
    public function index(){
        $state = I("get.zt"); //设置标题
        $this->assign("title", self::$status[$state]);

        //获取搜索条件
        $filter = $this->fn_GenerateFilter($state);
        $where = $this->fn_GenerateWhereClause($state);
        $order = $this->fn_GenerateOrderBy();

        //分页信息
        $pageNum = intval(I("post.page"));
        $pageNum = $pageNum==0?1:$pageNum;
        $page = $this->fn_GetPageInfo($where, $pageNum);
        $this->assign("page", $page);

        //搜索出公租房申请
        $limit = ($page['from']-1).",".$page['num'];
        $list = M("gzf g")->field("g.*, c.no as contact, h.name as house")
                        ->join(C('DB_PREFIX')."contact c on c.no=g.contact_no", "LEFT")
                        ->join(C('DB_PREFIX')."house h on h.name=g.house_no", "LEFT")
                        ->where($where)
                        ->order($order)->limit($limit)->select();
        //echo M()->getLastSql();
        for($i=0; $i<count($list); $i++){
            $list[$i]["offset"] = $page['from']-1+$i;
        }
        $this->assign("list", $list);

        //设置搜索条件
        $this->assign("filter", $filter);
        $this->assign("status", self::$status);
        $this->assign("area", $this->fn_GetAreaDict());
        $this->assign("community", $this->fn_GetCommunityDict());

        $this->display(self::$tmpl[$state]["listview"]);
    }

    public function fn_GetCommunityList(){
        return M("community")->order("no asc")->select();
    }

    public function fn_GetCommunityDict(){
        $dict = array();
        $res = M("community")->order("no asc")->select();
        for($i=0; $i<count($res); $i++) {
            $dict[$res[$i]["id"]] = $res[$i]["name"];
        }
        return $dict;
    }

    protected function fn_GetFamilyInfo($gzfNo){
        return M("gzf_family")->where(array("gzf_no"=>$gzfNo))->order("id asc")->select();
    }

    protected function fn_GetDocInfo($gzfNo){
        return M("gzf_doc")->where(array("gzf_no"=>$gzfNo))->order("id asc")->select();
    }

    protected function fn_GetLogInfo($gzfNo){
        return M("gzf_log")->where(array("gzf_no"=>$gzfNo))->order("id desc")->select();
    }

    protected function fn_GetZjInfo($gzfNo){
        return M("gzf_attach")->where(array("gzf_no"=>$gzfNo))->find();
    }

    /**
     * 公租房申请信息 -> formview
     */
    public function detail(){
        //读取廉租房申请信息
        $dataId = I("get.id");
        $gzf = M("gzf g")->where(array("id"=>$dataId))->find();
        $this->assign("gzf", $gzf);
        $this->assign("status", self::$status);

        //获取联合审批记录
        $model = parent::$spdw[self::$name]["model"];
        $this->assign("lhsp", $this->fn_GetLhspList($model, $gzf["no"]));

        //获取电子证件信息
        $this->assign("zj", $this->fn_GetZjInfo($gzf["no"]));

        //搜索出共同申请人信息、证件证明登记表、审批记录
        $family = $this->fn_GetFamilyInfo($gzf["no"]);
        $this->assign("family", $family);
        $doc = $this->fn_GetDocInfo($gzf["no"]);
        $this->assign("doc", $doc);
        $log = $this->fn_GetLogInfo($gzf["no"]);
        $this->assign("log", $log);
        $this->assign("area", $this->fn_GetAreaDict());
        $this->assign("areaOptions", json_encode($this->fn_GetAreaList()));
        $this->assign("community", $this->fn_GetCommunityDict());
        $this->assign("communityOptions", json_encode($this->fn_GetCommunityList()));

        //查询条件
        $state = I("get.state");
        $where = $this->fn_GenerateWhereClause($state);
        $order = $this->fn_GenerateOrderBy();
        $pageInfo = $this->fn_GetPageInfo($where);

        //待联合审查时，查检相关部门是否审核完毕
        if($state=='accept' and $this->fn_IsLhspFinished($gzf)){
            $this->assign("lhspState", "联合审批完毕");
            $this->assign("failedLhsp", $this->fn_GetFailedLhsp($gzf));
        }

        //设置已分配房源，已签合同信息
        if($state=="pass4" && $gzf["nsno"]){
            $nsInfo = M("gzf g")->field("c.jsrq, h.name as hno, g.contact_id, c.no as cno, c.zj, c.mzj, c.yj")
                                ->join(C('DB_PREFIX')."contact c on c.id=g.contact_id", "LEFT")
                                ->join(C('DB_PREFIX')."house h on h.id=g.house_id", "LEFT")
                                ->where(array("g.no"=>$gzf["nsno"]))
                                ->find();
            $this->assign("nsInfo", $nsInfo);
        }

        //设置押金，欠款信息
        if($state=="done"){
            $contact = M("contact")->where(array("id"=>$gzf["contact_id"]))->find();
            $this->assign("contact", $contact);
        }

        //页面信息
        $offset = intval(I("get.offset"));
        $nearby = $this->fn_FindNearbyItems($where, $order, $offset);
        $page = array_merge($pageInfo, $nearby);
        $this->assign("page", $page);

        // 设置上下文信息
        $context = $this->fn_GenerateContext($page);
        $this->assign("context", $context);
        $this->assign("shebaoOptions", json_encode(self::$shebao));
        $this->assign("genderOptions", json_encode(self::$gender));
        $this->assign("workStateOptions", json_encode(self::$workState));
        $this->assign("applierTypeOptions", json_encode(self::$applierType));
        $this->assign("houseStateOptions", json_encode(self::$houseState));
        $this->assign("opinion", $this->fn_GetOpinionList());

        //渲染加入黑名单对话框
        $this->Render_BlacklistDialog($gzf);
        $this->display(self::$tmpl[I("get.state")]["formview"]);
    }

    protected function fn_IsLhspFinished($apply){
        $lhspdw = parent::$spdw[ self::$name ]["spdw"]; //增加3条联合审查记录
        foreach ($lhspdw as $item) {
            if(!$apply[$item['field']]){
                return false;
            }
        }
        return true;
    }

    protected function fn_GetFailedLhsp($apply){
        $lhspdw = parent::$spdw[ self::$name ]["spdw"]; //增加3条联合审查记录
        foreach ($lhspdw as $key => $item) {
            if($apply[$item['field']]=='未通过'){
                return $key;
            }
        }
        return null;
    }

    protected function fn_GetLhspList($model, $no){
        $where = array("model"=>$model, "no"=>$no);
        return M("lhsp")->where($where)->select();
    }

    public function fn_GetAreaList(){
        return M("area")->order("seq asc")->select();
    }

    public function fn_GetAreaDict(){
        $dict = array();
        $res = M("area")->order("seq asc")->select();
        for($i=0; $i<count($res); $i++) {
            $dict[$res[$i]["id"]] = $res[$i]["name"];
        }
        return $dict;
    }

    /**
     * AJAX方法，在formview中对申请进行资格初审
     */
    public function ajax_zgcs(){
        $gzfId = I("post.id");
        if(!$gzfId){
            $this->error("缺少必要参数");
        }

        $trans = M();
        $trans->startTrans();   // 开启事务
        try{
            //添加审批记录
            $gzf = M("gzf")->where(array("id"=>$gzfId))->find();
            $res = $this->fn_AddShenpiLog($gzf['no'], "资格初审", I("post.spyj"), I("post.spzt"));
            if(!$res){
                $this->error("系统错误，日志写入失败");
            }

            //修改申请状态为failed
            if(I("post.spzt")=="同意"){
                $lhspdw = parent::$spdw[ self::$name ]["spdw"][ $gzf["applier_type"] ]; //增加3条联合审查记录
                foreach($lhspdw as $key){
                    if($key=='人社局企业保险股' && $gzf['work_state']=='机关事业单位'){
                        continue; //机关事业单位不需要 人社局企业保险股审批
                    }
                    if($key=='人社局机关事业保险股' && $gzf['work_state']!='机关事业单位'){
                        continue; //非机关事业单位不需要 人社局机关事业保险股审
                    }
                    $res = $this->fn_AddLhcsRecord($key, $gzf);
                    if(!$res){
                        $this->error("系统错误，联合审批信息写入失败");
                    }
                }
                $res = $this->fn_MarkApplyAccept($gzf['no']);
            }else{
                $res = $this->fn_MarkApplyFailed($gzf, I("post.spyj"));
            }
            if(!$res){
                $this->error("系统错误，请稍候重试");
            }
            $trans->commit();   // 提交事务

            if(I("post.sendMsg")){
                //发送短信
                $res = SmsApiController::SendMsg("1000", $gzf['phone'], $gzf);
                if(!$res['state']){
                    \Think\Log::write("短信发送失败：".$res['info']);
                }else{
                    \Think\Log::write("短信发送成功：".$res['phone']);
                }
            }
            $this->success("操作成功");
        }catch (Exception $e){
            $trans->rollback();   // 开启事务
            $this->error($e->getMessage());
        }
    }

    protected function fn_GetOpinionList(){
        return M("opinion")->where()->order("seq asc")->select();
    }

    protected function fn_AddLhcsRecord($spdw, $gzf){
        $lhsp = array(
            "spdw" => $spdw,
            "type" => self::$name,
            "model" => parent::$spdw[ self::$name ]["model"],
            "no" => $gzf["no"],
            "name" => $gzf["name"],
            "marriage" => $gzf["marriage"],
            "estate" => $gzf['estate'],
            "work_state" => $gzf['work_state'],
            "company" => $gzf['company'],
            "gender" => $gzf["gender"],//18位的身份证号码17位代表性别 积数为男，偶数为女
            "idno" => $gzf["idno"],
            "phone" => $gzf["phone"],
            "sqtime" => time(),
        );
        $peiou = $this->fn_GetPeiOuInfo($gzf['no']);
        if($peiou){
            $lhsp['name2'] = $peiou['name'];
            $lhsp['idno2'] = $peiou['idno'];
            $lhsp['gender2'] = $peiou['gender'];
            $lhsp['phone2'] = $peiou['phone'];
        }
        return M("lhsp")->add($lhsp);
    }

    public function fn_GetPeiOuInfo($no){
        $where = array("gzf_no"=>$no, "relation"=>"配偶");
        return M("gzf_family")->field("name,idno,gender,phone")->where($where)->find();
    }

    /**
     * AJAX方法，在formview中对申请进行联合审查
     */
    public function ajax_lhcs(){
        $gzfId = I("post.id");
        if(!$gzfId){
            $this->error("缺少必要参数");
        }

        $trans = M();
        $trans->startTrans();   // 开启事务
        try{
            //添加审批记录
            $gzf = M("gzf")->where(array("id"=>$gzfId))->find();
            $res = $this->fn_AddShenpiLog($gzf['no'], "联合审查", I("post.spyj"), I("post.spzt"));
            if(!$res){
                $this->error("系统错误，日志写入失败");
            }

            //修改申请状态为failed
            if(I("post.spzt")=="同意"){
                $res = $this->fn_MarkApplyPass1($gzf['no']);
            }else{
                $res = $this->fn_MarkApplyFailed($gzf, I("post.spyj"));
            }
            if(!$res){
                $this->error("系统错误，请稍候重试");
            }

            $trans->commit();   // 提交事务
            $result = array("status"=>true);
            $this->ajaxReturn($result);
        }catch (Exception $e){
            $trans->rollback();   // 开启事务
            $this->error($e->getMessage());
        }
    }

    protected function fn_MarkApplyAccept($no){
        $data = array("state"=>"accept", "utime"=>time());
        return M("gzf")->where(array("no"=>$no))->save($data);
    }

    protected function fn_MarkApplyPass1($no){
        $data = array("state"=>"pass1", "utime"=>time());
        return M("gzf")->where(array("no"=>$no))->save($data);
    }

    protected function fn_MarkApplyPass2($no){
        $data = array("state"=>"pass2", "utime"=>time());
        return M("gzf")->where(array("no"=>$no))->save($data);
    }

    protected function fn_MarkApplyPass3($no){
        $data = array("state"=>"pass3", "utime"=>time());
        return M("gzf")->where(array("no"=>$no))->save($data);
    }

    protected function fn_MarkApplyPass4($no){
        $data = array("state"=>"pass4", "utime"=>time());
        return M("gzf")->where(array("no"=>$no))->save($data);
    }

    protected function fn_MarkApplyFailed($apply, $reason){
        $data = array("state"=>"failed", "utime"=>time());
        $res = M("gzf")->where(array("no"=>$apply['no']))->save($data);
        if($res){
            //发送短信
            $params = array("no"=>$apply['no'], "reason"=>$reason);
            $sms = SmsApiController::SendMsg("2000", $apply['phone'], $params);
            if(!$sms['state']){
                \Think\Log::write("短信发送失败：".$sms['info']);
            }else{
                \Think\Log::write("短信发送成功：".$sms['phone']);
            }
        }
        return $res;
    }

    /**
     * AJAX方法，在formview中对申请进行 房产局初审
     */
    public function ajax_fcjcs(){
        $gzfId = I("post.id");
        if(!$gzfId){
            $this->error("缺少必要参数");
        }

        $trans = M();
        $trans->startTrans();   // 开启事务
        try{
            //添加审批记录
            $gzf = M("gzf")->where(array("id"=>$gzfId))->find();
            $res = $this->fn_AddShenpiLog($gzf['no'], "房产局初审", I("post.spyj"), I("post.spzt"));
            if(!$res){
                $this->error("系统错误，日志写入失败");
            }

            //修改申请状态为failed
            if(I("post.spzt")=="同意"){
                $res = $this->fn_MarkApplyPass2($gzf['no']);
            }else{
                $res = $this->fn_MarkApplyFailed($gzf, I("post.spyj"));
            }
            if(!$res){
                $this->error("系统错误，请稍候重试");
            }

            $trans->commit();   // 提交事务
            $result = array("status"=>true);
            $this->ajaxReturn($result);
        }catch (Exception $e){
            $trans->rollback();   // 开启事务
            $this->error($e->getMessage());
        }
    }

    /**
     * AJAX方法，在formview中对申请进行 房产局复审
     */
    public function ajax_fcjfs(){
        $gzfId = I("post.id");
        if(!$gzfId){
            $this->error("缺少必要参数");
        }

        $trans = M();
        $trans->startTrans();   // 开启事务
        try{
            //添加审批记录
            $gzf = M("gzf")->where(array("id"=>$gzfId))->find();
            $res = $this->fn_AddShenpiLog($gzf['no'], "房产局复审", I("post.spyj"), I("post.spzt"));
            if(!$res){
                $this->error("系统错误，日志写入失败");
            }

            //修改申请状态为failed
            if(I("post.spzt")=="同意"){
                $res = $this->fn_MarkApplyPass3($gzf['no']);
            }else{
                $res = $this->fn_MarkApplyFailed($gzf, I("post.spyj"));
            }
            if(!$res){
                $this->error("系统错误，请稍候重试");
            }

            $trans->commit();   // 提交事务
            $result = array("status"=>true);
            $this->ajaxReturn($result);
        }catch (Exception $e){
            $trans->rollback();   // 开启事务
            $this->error($e->getMessage());
        }
    }

    /**
     * AJAX方法，在formview中对申请进行 资格公示
     */
    public function ajax_zggs(){
        $gzfId = I("post.id");
        if(!$gzfId){
            $this->error("缺少必要参数");
        }

        $trans = M();
        $trans->startTrans();   // 开启事务
        try{
            //添加审批记录
            $gzf = M("gzf")->where(array("id"=>$gzfId))->find();
            $res = $this->fn_AddShenpiLog($gzf['no'], "资格公示", I("post.spyj"), I("post.spzt"));
            if(!$res){
                $this->error("系统错误，日志写入失败");
            }

            //修改申请状态为failed
            if(I("post.spzt")=="同意"){
                $res = $this->fn_MarkApplyPass4($gzf['no']);
            }else{
                $res = $this->fn_MarkApplyFailed($gzf, I("post.spyj"));
            }
            if(!$res){
                $this->error("系统错误，请稍候重试");
            }

            $trans->commit();   // 提交事务
            $result = array("status"=>true);
            $this->ajaxReturn($result);
        }catch (Exception $e){
            $trans->rollback();   // 开启事务
            $this->error($e->getMessage());
        }
    }

    /**
     * 添加廉租房的审批日志
     * @param $no 申请编号
     * @param $node 审批节点
     * @param $opinion 审批意见
     * @param string $state 审批状态
     * @return mixed
     */
    protected function fn_AddShenpiLog($no, $node, $opinion, $state='同意'){
        $log = array(
            "gzf_no" => $no,
            "spr" => self::$u["username"],
            "spdw" => self::$u["company_name"],
            "sphj" => $node,
            "spyj" => $opinion,
            "spzt" => $state,
            "sptime" => time(),
        );
        return M("gzf_log")->add($log);
    }

    /**
     * AJAX方法，在listview中进行批量删除信息
     */
    public function ajax_del(){

        $dataIds = I("post.info");
        if(count($dataIds)==0){
            $this->error("您还没有选中任何记录");
        }

        $map["id"] = array("in", $dataIds);
        $res = M("gzf")->where($map)->delete();
        if($res){
            $this->success("操作成功");
        }else{
            $this->error("系统错误");
        }
    }

    /**
     * AJAX方法，在formview中进行单条信息删除
     */
    public function ajax_del2(){
        //根据ID删除单位记录
        $id = I("post.id");
        $res = M("gzf g")->where(array("id" => $id))->delete();
        if(!$res){
            $this->error("系统错误");
        }

        //分页信息
        $where = $this->fn_GenerateWhereClause();
        $order = $this->fn_GenerateOrderBy();
        $total = $this->fn_GetTotalItems($where);
        if($total==0){
            $this->success(null);
        }

        //计算下一条记录的offset
        $offset = intval(I("post.offset"));
        $offset = ($offset>=$total-1)?0:$offset;

        //获取下一记录的信息
        $nextItem = $this->fn_GetInfoByOffset($offset, $where, $order);
        $this->success($nextItem["id"]); //返回下一条记录的ID
    }

    /**
     * AJAX方法，通过ID来修改信息
     */
    public function ajax_UpdateInfoById(){
        $data = I("post.data");
        $data['utime'] = time();
        $dataId = I("post.id");
        $res = M("gzf")->where(array("id"=>$dataId))->save($data);
        //$this->error(M()->getLastSql());
        if($res){
            $this->success("0k, 200");
        }else{
            $this->error("更新异常，请稍候再试");
        }
    }

    /**
     * 房屋自动补全搜索算法
     */
    public function ajax_SearchHouseByName(){
        $h = array();
        $where = array("name"=>array("like", "%".I("get.house")."%"));
        $house = M("house")->where($where)->select();
        foreach ($house as $v){
            array_push($h, array("value"=>$v["name"], "data"=>$v));
        }
        $this->ajaxReturn(array('query'=>I("get.house"),'suggestions' => $h));
    }

    /**
     * 合同自动补全搜索算法，通过合同编号查找(未签订状态)
     */
    public function ajax_SearchContactByNo(){
        $h = array();
        //默认是匹配未签订的合同
        $where = array("state"=>"未签订", "no"=>array("like", "%".I("get.no")."%"));
        $house = M("contact")->where($where)->select();
        foreach ($house as $v){
            array_push($h, array("value"=>$v["no"], "data"=>$v));
        }
        $this->ajaxReturn(array('query'=>I("get.no"),'suggestions' => $h));
    }

    /**
     * 导出公示名单，导出规则：
     * 1.如果选中了某些行，则导出所选中的项；
     * 2.如果没有选中任意行，则导出所有的项；
     */
    public function export(){
        $where = array();
        if(I("get.type")=="gsmd"){
            $where = array('state' => 'pass3');
        }else if(I("get.type")=="yhmd"){
            $where = array('state' => 'pass4');
        }

        $ids = I("get.ids");
        if($ids){
            $where = "id in (".$ids.")'";
        }else if(I("get.field") & I("get.value")){
            $where[I("get.field")] = array("like", "%".I("get.value")."%");
        }
        $this->assign("type", I("get.type"));
        $this->assign("where", serialize($where));
        $this->display();
    }

    /**
     * 导出摇号名单
     * @throws \Exception
     */
    public function action_ExportYhmd(){
        $where = unserialize( html_entity_decode(I("post.where")) ); //"被转换成了html实体，需要decode
        $data = M("gzf g")->field("g.no, g.name, g.idno, g.phone, c.name as community")
            ->join(C("DB_PREFIX")."community c on c.id=g.community_id", "LEFT")
            ->where($where)->order("no asc")->select();
        $fileName = $this->fn_ExportYhmdExcel($data);
        $this->success($fileName);
    }

    public function fn_ExportYhmdExcel($data){
        import("Common.ORG.Util.PHPExcel");
        import("Common.ORG.Util.PHPExcel.Writer.Excel5");
        import("Common.ORG.Util.PHPExcel.IOFactory.php");

        $inputFileName = "./db/xlsx_tpl/yhmd.xls";
        try { //  Read your Excel workbook
            $inputFileType = \PHPExcel_IOFactory::identify($inputFileName);
            $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
            $objPHPExcel = $objReader->load($inputFileName);
        } catch(Exception $e) {
            die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
        }

        //$objPHPExcel = new \PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        $rowCount = 3; //从第3行开始写数据

        for($i=0; $i<count($data); $i++){
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $data[$i]["no"]);
            $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $data[$i]["name"]);
            $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, " ".$data[$i]["idno"]);
            $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, " ".$data[$i]["phone"]);
            $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, " ".$data[$i]["community"]);
            $rowCount += 1;
        }

        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $newFile = '/db/export/'.time().'.xlsx';
        $objWriter->save(".".$newFile);
        return $newFile;
    }

    /**
     * 导出xls入口
     * @throws \Exception
     */
    public function ajax_export(){

        if(I("post.type")=="gsmd"){
            $this->action_ExportGsmd();
        }else if(I("post.type")=="yhmd"){
            $this->action_ExportYhmd();
        }
        $this->error("请选择导出数据类型");
    }

    /**
     * 导出公示名单
     * @throws \Exception
     */
    public function action_ExportGsmd(){
        $where = unserialize( html_entity_decode(I("post.where")) ); //"被转换成了html实体，需要decode
        $data = M("gzf")->field("no, name, idno, phone")->where($where)->order("no asc")->select();
        $fileName = $this->fn_ExportGsmdExcel($data);
        $this->success($fileName);
    }

    public function fn_ExportGsmdExcel($data){
        import("Common.ORG.Util.PHPExcel");
        import("Common.ORG.Util.PHPExcel.Writer.Excel5");
        import("Common.ORG.Util.PHPExcel.IOFactory.php");

        $inputFileName = "./db/xlsx_tpl/gsmd.xls";
        try { //  Read your Excel workbook
            $inputFileType = \PHPExcel_IOFactory::identify($inputFileName);
            $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
            $objPHPExcel = $objReader->load($inputFileName);
        } catch(Exception $e) {
            die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
        }

        //$objPHPExcel = new \PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        $rowCount = 3; //从第3行开始写数据

        for($i=0; $i<count($data); $i++){
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $data[$i]["no"]);
            $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $data[$i]["name"]);
            $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, " ".substr_replace($data[$i]["idno"], '********',6,8));
            $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, " ".substr_replace($data[$i]["phone"], '****',3,4));
            $rowCount += 1;
        }

        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $newFile = '/db/export/'.time().'.xlsx';
        $objWriter->save(".".$newFile);
        return $newFile;
    }

    /**
     *  辅助方法，通过$_POST的值来生成where查询条件
     */
    private function fn_GenerateWhereClause($state){
        $where = array();
        if($_REQUEST['where']){
            $where = unserialize($_REQUEST['where']);
        }
        if($state!="all"){  //申请汇总     lzf简称l，可以兼容多表查询
            $where['g.state'] = $state;
        }
        if(I("request.value")) {
            if (I("request.field") == "category") { //多表查询处理
                $cateIds = $this->fn_SelectCategoryByName(I("request.value"));
                $where["g.".I("request.field")] = array("in", $cateIds);
            } else {
                $where["g.".I("request.field")] = array("like", "%" . I("request.value") . "%");
            }
        }
        return $where;
    }

    private function fn_SelectCategoryByName($name){
        $where["name"] =  array("like", "%".$name."%");
        $result = M("affairscategory")->where($where)->field("id")->select();
        $cateIds = array();
        for($i=0; $i<count($result); $i++){
            array_push($cateIds, $result[$i]["id"]);
        }
        return $cateIds;
    }

    /**
     *  辅助方法，通过$_POST的值来生成order by子句
     */
    private function fn_GenerateOrderBy(){
        $order = "id desc";
        if(I("request.order_field") && I("request.order_sort")){
            $order = I("request.order_field") . " " . I("request.order_sort");
        }
        return $order;
    }

    /**
     *  辅助方法，通过$_POST的值来生成Filter (listview)
     */
    private function fn_GenerateFilter($state){
        if(IS_POST){
            return array(
                "page" => I("post.page"),
                "where" => $_REQUEST["where"],
                "field" => I("post.field"),
                "value" => I("post.value"),
                "state" => I("post.state"),
                "order_field" => I("post.order_field"),
                "order_sort" => I("post.order_sort"),
            );
        }
        return array(
            "page" => 1,
            "where" => $_REQUEST["where"],
            "state" => $state,
            "order_field" => "id",
            "order_sort" => "desc",
        );
    }

    /**
     * 辅助方法，通过$_POST的值来生成Context (formvier)
     * @return array
     */
    private function fn_GenerateContext($page){
        return array(
            "id" => I("request.id"),
            "field" => I("request.field"),
            "state" => I("request.state"),
            "value" => I("request.value"),
            "order_field" => I("request.order_field"),
            "order_sort" => I("request.order_sort"),
            "page" => I("request.page"),
            "offset" => I("request.offset"),
            "where" => $_REQUEST["where"],
            "index" => I("get.offset")-($page["curPage"]-1)*$page["num"]+1
        );
    }

    /**
     * 辅助方法，通过where,order,offset来查询信息
     * @param $offset
     * @param $where
     * @param $order
     * @return mixed
     */
    private function fn_GetInfoByOffset($offset, $where, $order){
        $limit = $offset.",1";
        $res =  M("gzf g")->field("id")->where($where)->order($order)->limit($limit)->select();
        if($res){
            return $res[0];
        }
        return array();
    }

    /**
     * 辅助方法，通过where, curpage, num来生成分页信息
     * @param $where
     * @param int $curPage
     * @param int $num
     * @return array
     */
    private function fn_GetPageInfo($where, $curPage=1, $num=15){
        $total = M("gzf g")->where($where)->count();
        $totalPage = ceil($total/$num);
        $nextPage = $curPage==$totalPage?1:($curPage+1);
        $prevPage = $curPage==1?$totalPage:($curPage-1);
        $to = $curPage*$num;
        $res = array(
            "total" => $total,
            "num" => $num,
            "from" => (($curPage-1)*$num+1),
            "to" => $to>$total?$total:$to,
            "curPage" => $curPage,
            "nextPage" => $nextPage,
            "prevPage" => $prevPage
        );
        return $res;
    }

    /**
     * 辅助方法，通过where来统计总的信息数量
     * @param array $where
     * @return mixed
     */
    public function fn_GetTotalItems($where=array()){
        return M("gzf g")->where($where)->count();
    }

    /**
     * 辅助方法，通过where, order, offset查询相邻两条数据
     * @param $where
     * @param $offset
     */
    public function fn_FindNearbyItems($where, $order, $offset){
        $total = M("gzf g")->where($where)->count();
        $prevOffset = $offset>0?$offset-1:$total-1;
        $nextOffset = $offset>=$total-1?0:$offset+1;
        $prevItem = $this->fn_GetInfoByOffset($prevOffset, $where, $order);
        $nextItem = $this->fn_GetInfoByOffset($nextOffset, $where, $order);
        return array(
            "prev" => array("id"=>$prevItem["id"],"offset"=>$prevOffset),
            "next" => array("id"=>$nextItem["id"],"offset"=>$nextOffset),
        );
    }

}